package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {

    @Select("select\n" +
            "    hour(create_time)`name`,\n" +
            "    count(*)`value`\n" +
            "from user_chapter_process\n" +
            "group by hour(create_time)\n" +
            "order by hour(create_time)")
    List<Vo> getList1();

    @Select("select\n" +
            "    ci.course_name`name`,\n" +
            "    count(distinct te.user_id)`count`,\n" +
            "    sum(te.score)/count(distinct te.user_id)`avgscore`,\n" +
            "    sum(te.duration_sec)/count(distinct te.user_id)`avgtime`\n" +
            "from test_exam te\n" +
            "         left join test_paper tp on tp.id = te.paper_id\n" +
            "         left join course_info ci on tp.course_id = ci.id\n" +
            "group by ci.course_name")
    List<Vo1> getList2();

    @Select("select count(*) from payment_info where date(create_time)>'2021-04-22';")
    Double getList3();

    @Select("select\n" +
            "    ci.course_name`name`,\n" +
            "    avg(ri.review_stars)`avg`,\n" +
            "    count(distinct ri.user_id)`count`,\n" +
            "    sum(if(ri.review_stars=5,1,0))`sum`,\n" +
            "    sum(if(ri.review_stars=5,1,0))/count(distinct ri.user_id)`lv`\n" +
            "from review_info ri\n" +
            "left join course_info ci on ci.id = ri.course_id\n" +
            "group by ci.course_name")
    List<Vo2> getList4();
}
